ASPX, Gridview Group by

Question

In my aspx +vb with sql web i have a gridview . In gridview i want to group LPR_NO as shown below

I want all these field data in gridview but i want to group only lpr_No

SELECT LP_ION.Originator,LP_ION.Originator_ID,Convert(varchar(10), LP_ION.Date,110) as Initiated_Dt,LP_ION.LPR_No,LP_ION.Product,LP_ION.AU,LP_ION.Qty_Req, 
    LP_ION.Qty_Issue,LP_ION.Qty_NA,  LP_ION.Status as ION_Status, LPR_Initiate.Ini_Status, LPR_Initiate.Rec_Status,LPR_Initiate.Approve_Status,LP_ION.CTS_vet_Status,LP_ION.WSG_Hclk_Status ,LPR_Initiate.CFA_Status ,LPR_Initiate.RFP_Status  ,LPR_Initiate.CFA_Status ,LPR_Initiate.SO_Status  
    FROM LP_ION JOIN LPR_Initiate ON LP_ION.LPR_No = LPR_Initiate.LPR_No
    WHERE LPR_Initiate.Approve_Status='Yes'
    ORDER BY LPR_NO DESC

Answer 1

This is one of those cases in which I suggest using a ListView.

The GridView (or ListView) unfortunately does not have a built in grouping option.

There are 2 approaches I have used with success.

Consider using a Repeater, and then in the repeater, nest the GridView. This of course results in considerable more processing, and also results in a whole new query for each grouping. It not too bad, but not all that great.

The next suggestion?

Go with a ListView. It turns out it really quite easy to hide, or show a "whole" row in a list view, and thus you make that "extra" row the group heading.

Of course this does mean adopting a ListView. You can let the wizards create the ListView for you, then delete all of the templates (you don't need them). And then of course remove the SQL data source, and use code to fill out the ListView.

So, the markup is thus this:

    <asp:ListView ID="ListView1" runat="server" >
        <ItemTemplate>
            <tr id="MyGroupHeading" runat="server" 
                style="background-color:black;color:white" >
                <td colspan="4">
                    <asp:Label ID="lCity" 
                        runat="server" 
                        Text='<%# "Hotels For city = " & Eval("City") %>' />
                </td>
            </tr>
            <tr>
                <td><asp:Label ID="lFirst" runat="server" Text='<%# Eval("FirstName") %>' /></td>
                <td><asp:Label ID="lLast" runat="server" Text='<%# Eval("LastName") %>' /></td>
                <td><asp:Label ID="lHotel" runat="server" Text='<%# Eval("HotelName") %>' /></td>
                <td><asp:Label ID="lDescript" runat="server" Text='<%# Eval("Description") %>' /></td>
            </tr>
        </ItemTemplate>

        <LayoutTemplate>
            <table id="itemPlaceholderContainer" runat="server" border="0" 
                class="table" style="width:40%" >
                <tr runat="server" style="">
                    <th runat="server" style="width:90px">First Name</th>
                    <th runat="server" style="width:90px">Last Name</th>
                    <th runat="server">Hotel Name</th>
                    <th runat="server">Hotel Description</th>
                </tr>
                <tr id="itemPlaceholder" runat="server">
                </tr>
            </table>
        </LayoutTemplate>
    </asp:ListView>

And the code behind is thus this:

    Dim strMyGroup As String

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If IsPostBack = False Then
            loadGrid()
        End If

    End Sub

    Sub loadGrid()

        strMyGroup = ""

        Dim strSQL As String =
             "SELECT * FROM tblHotelsA ORDER BY City, HotelName"

        ListView1.DataSource = MyRst(strSQL)
        ListView1.DataBind()

    End Sub

Now, all we add is this code to the data bind event:

    Protected Sub ListView1_ItemDataBound(sender As Object, e As ListViewItemEventArgs) Handles ListView1.ItemDataBound

        If e.Item.ItemType = ListViewItemType.DataItem Then

            Dim strNewGroup = DirectCast(e.Item.FindControl("lCity"), Label).Text

            Dim MyTr As HtmlTableRow = e.Item.FindControl("MyGroupHeading")
            If strNewGroup <> strMyGroup Then
                ' a new group - 
                strMyGroup = strNewGroup
                MyTr.Visible = True
            Else
                MyTr.Visible = False
            End If

        End If

    End Sub

So, in the above example, we have a list of hotels, and we want to group by City, and then show Hotels for each city.

The above results in this:

So adopting a ListView I think gives rise to solutions that don't require much code, and you have near endless options for what markup you want in that "grouping" row. It then becomes a simple matter to hide, or show that extra row based on a group change - in this example City.

Answer 2

Hi @Baiju EP

First put the data into the GridView, and then compare and judge each element in the GridView. There is no need to use SQL statements too much here.

Dim count As Integer = 0

    For i As Integer = 1 To GridView1.Rows.Count - 1

        If GridView1.Rows(i).Cells(2).Text = GridView1.Rows(i - 1).Cells(2).Text Then
            count += 1
            GridView1.Rows(i).Cells(0).Visible = False
            GridView1.Rows(i).Cells(1).Visible = False
            GridView1.Rows(i).Cells(2).Visible = False
        Else
            GridView1.Rows(i - count - 1).Cells(0).RowSpan = count + 1
            GridView1.Rows(i - count - 1).Cells(1).RowSpan = count + 1
            GridView1.Rows(i - count - 1).Cells(2).RowSpan = count + 1
            count = 0
        End If
    Next

Best regards,
Qi You

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.